﻿CREATE PROCEDURE [acms].[Post_GetAllByParentIdAndTopicId]
	@ParentId int,
	@TopicId int,
	@IncludeChildren bit,
	@StartIndex int,
	@PageSize int,
	@TotalRecordsOut int Output
AS
	declare @tmp table (Id int, RowNumber int identity(1,1))

	if @IncludeChildren = 0 begin
		insert into @tmp
			(ID)
		SELECT Id
		FROM [Post_View] a
			inner join [PostTopic_View] b on a.Id = b.PostId
		Where a.ParentId = @ParentId
			and b.TopicId = @TopicId
		ORDER By a.Sequence
	end
	else begin
		declare @ParentPath varchar(255)
		select @ParentPath = ParentPath + Name + '%' From Post_View Where Id = @ParentId
		
		insert into @tmp
			(Id)
		SELECT (Id)
		FROM [Post_View] a
			inner join [PostTopic_View] b on a.Id = b.PostId
		Where a.ParentPath Like @ParentPath
			and b.TopicId = @TopicId
		ORDER By a.ParentSequence, a.Sequence
	end
	
	if @PageSize = 0 begin
		select b.* 
		from @tmp a
			inner join [Post_View] b on a.Id = b.Id
		order by a.RowNumber
		set @TotalRecordsOut = @@RowCount
	end
	else begin
		select b.* 
		from @tmp a
			inner join [Post_View] b on a.Id = b.Id
		where a.RowNumber between @StartIndex + 1 and @StartIndex + @PageSize
		order by a.RowNumber
		
		select @TotalRecordsOut = Count(Id) From @tmp
	end
	
RETURN 0;